﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CBMS.Interfaces;
using CBMS.Models;
using System.Data.Objects.DataClasses;
using System.Data.Objects;
using System.Collections;
using CBMS.Objects;
using System.Data.Objects.SqlClient;
using CBMS.Common;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data;
using System.Globalization;

namespace CBMS.Business
{
    public class StaffRepository : BaseEntityRepository<Staff>, IStaffRepository
    {
        public StaffRepository()
            : base()
        {

        }
        public Boolean DeleteAbilityOfStaff(int staffId)
        {
            try
            {
                int? result = DbContext.sp_DeleteAbilityOfStaffs(staffId).Single();
                if (result.HasValue && result.Value == 1)
                    return true;
            }
            catch
            {

            }
            return false;
        }
        public Boolean DeleteStaff(int staffId)
        {
            bool result = false;
            try
            {
                int? resultValue = DbContext.sp_DeleteStaffs(staffId).FirstOrDefault().resultValue;
                if (resultValue.HasValue && resultValue.Value == 1)
                {
                    result = true;
                }
            }
            catch
            {
                result = false;
            }
            return result;
        }
        public Boolean ChangeActive(int staffId)
        {
            try
            {
                Staff obj = DbContext.Staffs.SingleOrDefault(x => x.staff_id == staffId);
                if (obj != null)
                {
                    obj.is_active = !obj.is_active;
                    DbContext.SaveChanges();
                    return true;
                }
            }
            catch
            {

            }
            return false;
        }
        public IList<StaffDTO> SearchStaffsBySomeCondition(string condition, int pageIndex, int pageSize, string sortField, string sortExpression, ref int toTal)
        {
            try
            {
                var emp = DbContext.sp_SearchStaffs(condition, sortField, sortExpression, pageIndex, pageSize);
                toTal = 0;
                var query = (from c in emp
                             select new StaffDTO
                             {
                                 StaffId = c.staff_id,
                                 StaffCode = c.staff_code,
                                 FirstName = c.first_name,
                                 LastName = c.last_name,
                                 UserName = c.user_name,
                                 Email = c.email,
                                 BranchName = c.branch_name,
                                 RoleName = c.role_name,
                                 IsActive = c.is_active,
                                 Total = c.TotalRow
                             }).DefaultIfEmpty<StaffDTO>();
                var lstResult = query.ToList<StaffDTO>();
                if (lstResult == null || lstResult[0] == null)
                {
                    return new List<StaffDTO>();
                }
                else
                {
                    toTal = lstResult.FirstOrDefault().Total;
                }
                return lstResult;
            }
            catch
            {
                return new List<StaffDTO>();
            }
        }
        public IList<AbilityOfStaff> GetAbilityOfStaff(int staffId)
        {
            IList<AbilityOfStaff> lstResult = new List<AbilityOfStaff>();
            try
            {

                lstResult = (from c in DbContext.AbilityOfStaffs
                             where c.staff_id == staffId
                             select c).ToList<AbilityOfStaff>();
                return lstResult;
            }
            catch
            {
                return new List<AbilityOfStaff>();
            }
        }
        public Staff GetByID(int staffId)
        {
            Staff objResult = new Staff();
            try
            {
                objResult = DbContext.Staffs.SingleOrDefault(n => n.staff_id == staffId);
            }
            catch
            {
                objResult = new Staff();
            }
            return objResult;
        }
        public int Insert(Staff objStaff, List<AbilityOfStaff> lstAbility)
        {
            DbTransaction Transaction = null;
            using (EntityConnection DatabaseConnection = new EntityConnection(SettingsManager.ConnectString))
            {

                try
                {
                    if (DatabaseConnection.State == ConnectionState.Closed)
                    {
                        DatabaseConnection.Open();
                    }
                    var CurrentContext = new CBMSEntities(DatabaseConnection);
                    Transaction = CurrentContext.Connection.BeginTransaction();
                    int? staffId = DbContext.sp_InsertStaff(objStaff.branch_id, objStaff.staff_code, objStaff.first_name, objStaff.last_name, objStaff.user_name, objStaff.password, objStaff.role_code, objStaff.email, objStaff.phone, objStaff.birthday, objStaff.address, objStaff.suburb, objStaff.zip, objStaff.state_code, objStaff.country_code, objStaff.rate, objStaff.is_active, objStaff.created_by, objStaff.created_date, objStaff.modified_by, objStaff.modified_date).Single();
                    if (staffId.HasValue)
                    {
                        if (staffId.Value > 1)
                        {
                            if (lstAbility != null && staffId.Value > 0)
                            {
                                foreach (AbilityOfStaff objAbility in lstAbility)
                                {
                                    objAbility.staff_id = staffId.Value;
                                    DbContext.AddToAbilityOfStaffs(objAbility);
                                    DbContext.SaveChanges();
                                }
                            }
                            Transaction.Commit();
                            return staffId.Value;
                        }
                        else
                        {
                            Transaction.Rollback();
                            return 0;
                        }
                    }
                    else
                    {
                        Transaction.Rollback();
                        return 0;
                    }
                }
                catch (Exception ex)
                {
                    Transaction.Rollback();
                    return 0;
                }
                finally
                {
                    if (DatabaseConnection != null)
                    {
                        if (DatabaseConnection.State == ConnectionState.Open)
                        {
                            DatabaseConnection.Close();
                        }
                    }
                }
            }
            return 0;
        }
        public int Update(Staff objStaff, List<AbilityOfStaff> lstAbility)
        {
            DbTransaction Transaction = null;
            using (EntityConnection DatabaseConnection = new EntityConnection(SettingsManager.ConnectString))
            {

                try
                {
                    if (DatabaseConnection.State == ConnectionState.Closed)
                    {
                        DatabaseConnection.Open();
                    }
                    var CurrentContext = new CBMSEntities(DatabaseConnection);
                    Transaction = CurrentContext.Connection.BeginTransaction();
                    int? staffId = DbContext.sp_UpdateStaff(objStaff.staff_id, objStaff.branch_id, objStaff.staff_code, objStaff.first_name, objStaff.last_name, objStaff.user_name, objStaff.password, objStaff.role_code, objStaff.email, objStaff.phone, objStaff.birthday, objStaff.address, objStaff.suburb, objStaff.zip, objStaff.state_code, objStaff.country_code, objStaff.rate, objStaff.is_active, objStaff.created_by, objStaff.created_date, objStaff.modified_by, objStaff.modified_date).Single();
                    if (staffId.HasValue)
                    {
                        if (staffId.Value == 1)
                        {
                            //delete old ability
                            int? deleteAbilityResult = DbContext.sp_DeleteAbilityOfStaffs(objStaff.staff_id).Single();
                            if (deleteAbilityResult.HasValue)
                            {
                                if (deleteAbilityResult.Value == 1)
                                {
                                    //insert new ability
                                    if (lstAbility != null && staffId.Value > 0)
                                    {
                                        foreach (AbilityOfStaff objAbility in lstAbility)
                                        {
                                            objAbility.staff_id = objStaff.staff_id;
                                            DbContext.AddToAbilityOfStaffs(objAbility);
                                            DbContext.SaveChanges();
                                        }
                                    }
                                    Transaction.Commit();
                                    return staffId.Value;
                                }
                            }
                            Transaction.Rollback();
                            return 0;
                        }
                        else
                        {
                            Transaction.Rollback();
                            return 0;
                        }
                        // return staffId.Value;
                    }
                    else
                    {
                        Transaction.Rollback();
                        return 0;
                    }
                }
                catch (Exception ex)
                {
                    Transaction.Rollback();
                    return 0;
                }
                finally
                {
                    if (DatabaseConnection != null)
                    {
                        if (DatabaseConnection.State == ConnectionState.Open)
                        {
                            DatabaseConnection.Close();
                        }
                    }
                }
            }
            return 0;
        }
        public Boolean Login(int? brachId, string userName, string password)
        {
            try
            {
                password = BaseFunctions.Encrypte(password);
                Staff objStaff = DbContext.Staffs.SingleOrDefault(x => x.user_name == userName && x.password == password && x.is_active == true);
                if (objStaff == null)
                    return false;
                int staffBranch = 0;
                int loginBranch = 0;
                if (objStaff.branch_id != null)
                    staffBranch = objStaff.branch_id.Value;
                if (brachId != null)
                    loginBranch = brachId.Value;
                if (loginBranch != staffBranch)
                    return false;
                else return true;
            }
            catch (Exception ex)
            {
            }
            return false;
        }
        public Staff GetByUserName(string userName)
        {
            Staff objResult = new Staff();
            try
            {
                objResult = DbContext.Staffs.SingleOrDefault(n => n.user_name == userName);
            }
            catch
            {
                objResult = new Staff();
            }
            return objResult;
        }
    }
}